﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CrazyTalk.Data;
using CrazyTalk.IDAL;
using CrazyTalk.Models;
namespace CrazyTalk.MSSQLDAL
{
    public class MenuDAL : IMenuDAL
    {
        public MenuDAL()
        { }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string MenuID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Menu");
            strSql.Append(" where MenuID=@MenuID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32)			};
            parameters[0].Value = MenuID;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据输入的用户名和角色编号，获取用户的显示菜单列表
        /// </summary>
        /// <param name="userId">用户名</param>
        /// <param name="roleId">角色编号</param>
        /// <returns></returns>
        public DataTable GetMenuList(string userId, int roleId)
        {
            return DbHelperSQL.GetDataTableByPro("[Query_Menu_List]"
                , new SqlParameter("@UserId", SqlDbType.VarChar, 20) { Value = userId }
                , new SqlParameter("@RoleId", SqlDbType.Int) { Value = roleId });
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Menu model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Menu(");
            strSql.Append("MenuID,MenuUrl,MenuName,MenuLevel,ParentMenu,SortId,Status,PicName)");
            strSql.Append(" values (");
            strSql.Append("@MenuID,@MenuUrl,@MenuName,@MenuLevel,@ParentMenu,@SortId,@Status,@PicName)");
            SqlParameter[] parameters = {
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32),
					new SqlParameter("@MenuUrl", SqlDbType.NVarChar,200),
					new SqlParameter("@MenuName", SqlDbType.NVarChar,100),
					new SqlParameter("@MenuLevel", SqlDbType.NChar,1),
					new SqlParameter("@ParentMenu", SqlDbType.NVarChar,32),
					new SqlParameter("@SortId", SqlDbType.Int,4),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@PicName", SqlDbType.VarChar,30)};
            parameters[0].Value = model.MenuID;
            parameters[1].Value = model.MenuUrl;
            parameters[2].Value = model.MenuName;
            parameters[3].Value = model.MenuLevel;
            parameters[4].Value = model.ParentMenu;
            parameters[5].Value = model.SortId;
            parameters[6].Value = model.Status;
            parameters[7].Value = model.PicName;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Menu model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Menu set ");
            strSql.Append("MenuUrl=@MenuUrl,");
            strSql.Append("MenuName=@MenuName,");
            strSql.Append("MenuLevel=@MenuLevel,");
            strSql.Append("ParentMenu=@ParentMenu,");
            strSql.Append("SortId=@SortId,");
            strSql.Append("Status=@Status,");
            strSql.Append("PicName=@PicName");
            strSql.Append(" where MenuID=@MenuID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MenuUrl", SqlDbType.NVarChar,200),
					new SqlParameter("@MenuName", SqlDbType.NVarChar,100),
					new SqlParameter("@MenuLevel", SqlDbType.NChar,1),
					new SqlParameter("@ParentMenu", SqlDbType.NVarChar,32),
					new SqlParameter("@SortId", SqlDbType.Int,4),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@PicName", SqlDbType.VarChar,30),
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32)};
            parameters[0].Value = model.MenuUrl;
            parameters[1].Value = model.MenuName;
            parameters[2].Value = model.MenuLevel;
            parameters[3].Value = model.ParentMenu;
            parameters[4].Value = model.SortId;
            parameters[5].Value = model.Status;
            parameters[6].Value = model.PicName;
            parameters[7].Value = model.MenuID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string MenuID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Menu ");
            strSql.Append(" where MenuID=@MenuID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32)			};
            parameters[0].Value = MenuID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string MenuIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Menu ");
            strSql.Append(" where MenuID in (" + MenuIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Menu GetModel(string MenuID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 MenuID,MenuUrl,MenuName,MenuLevel,ParentMenu,SortId,Status,PicName from Menu ");
            strSql.Append(" where MenuID=@MenuID ");
            SqlParameter[] parameters = {
					new SqlParameter("@MenuID", SqlDbType.NVarChar,32)			};
            parameters[0].Value = MenuID;
            Menu model = new Menu();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["MenuID"] != null && ds.Tables[0].Rows[0]["MenuID"].ToString() != "")
                {
                    model.MenuID = ds.Tables[0].Rows[0]["MenuID"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MenuUrl"] != null && ds.Tables[0].Rows[0]["MenuUrl"].ToString() != "")
                {
                    model.MenuUrl = ds.Tables[0].Rows[0]["MenuUrl"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MenuName"] != null && ds.Tables[0].Rows[0]["MenuName"].ToString() != "")
                {
                    model.MenuName = ds.Tables[0].Rows[0]["MenuName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MenuLevel"] != null && ds.Tables[0].Rows[0]["MenuLevel"].ToString() != "")
                {
                    model.MenuLevel = ds.Tables[0].Rows[0]["MenuLevel"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ParentMenu"] != null && ds.Tables[0].Rows[0]["ParentMenu"].ToString() != "")
                {
                    model.ParentMenu = ds.Tables[0].Rows[0]["ParentMenu"].ToString();
                }
                if (ds.Tables[0].Rows[0]["SortId"] != null && ds.Tables[0].Rows[0]["SortId"].ToString() != "")
                {
                    model.SortId = int.Parse(ds.Tables[0].Rows[0]["SortId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PicName"] != null && ds.Tables[0].Rows[0]["PicName"].ToString() != "")
                {
                    model.PicName = ds.Tables[0].Rows[0]["PicName"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MenuID,MenuUrl,MenuName,MenuLevel,ParentMenu,SortId,Status,PicName ");
            strSql.Append(" FROM Menu where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 绕过报表权限控制，提取所有报表页面
        /// </summary>
        /// <param name="list">需要获取的报表的MenuID</param>
        /// <returns></returns>
        public DataSet GetReportsList(List<string> list)
        {
            DataSet ds = null;
            if (list.Count <= 0)
            {
                return ds;
            }
            string mid = "";
            for (int i = 0; i < list.Count; i++)
            {
                mid += list[i] + ",";
            }
            mid = mid.Substring(0, mid.Length - 1);
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * from Menu where MenuID in (" + mid + ") ");
            ds = DbHelperSQL.Query(strSql.ToString(), null);
            return ds;
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, List<string> fields, List<object> values, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" MenuID,MenuUrl,MenuName,MenuLevel,ParentMenu,SortId,Status,PicName ");
            strSql.Append(" FROM Menu where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Menu where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parm.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(List<string> fields, List<object> values, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.MenuID desc");
            }
            strSql.Append(")AS Row, T.*  from Menu T ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (i > 0)
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    else
                    {
                        strSql.Append(" where " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i], values[i]));
                }
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(List<string> fields,List<object> values)
        {
            if (fields.Count <= 0)
            {
                return false;
            }
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append("select count(1) from Menu");
            strSql.Append(" where 1=1 ");
            for (int i = 0; i < fields.Count; i++)
            {
                if (fields[i].StartsWith("like_"))
                {
                    strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                }
                else if (fields[i].StartsWith("not_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                }
                else if (fields[i].StartsWith("lt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                }
                else if (fields[i].StartsWith("gt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                }
                else
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                }
                parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
            }
            return DbHelperSQL.Exists(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListUnionMenu(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select *  FROM (
                            select m.*,me.MenuName as ParentName  from Menu m
                            left join Menu me on m.ParentMenu=me.MenuID
                            ) T where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        //更新需要的字段信息(支持 !=,>,<,=,where ...)
        public bool Update(List<string> fields, List<object> values)
        {
            if (fields == null || fields.Count == 0 || values == null || values.Count == 0)
            {
                return false;
            }
            StringBuilder strSql = new StringBuilder();
            StringBuilder where = new StringBuilder();
            strSql.Append(" update  Menu  set  ");
            where.Append("  where 1=1  ");
            List<SqlParameter> parm = new List<SqlParameter>();
            int aa = 0;
            for (int i = 0; i < fields.Count; i++)
            {
                if (fields[i].StartsWith("where_"))
                {
                    fields[i] = fields[i].Substring(6);
                    if (fields[i].StartsWith("like_"))
                    {
                        where.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        where.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        where.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        where.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        where.Append("  and  " + fields[i] + "=@" + fields[i] + "  ");
                    }
                }
                else
                {
                    if (aa > 0)
                    {
                        strSql.Append("  ,  ");
                    }
                    strSql.Append("  " + fields[i] + "=@" + fields[i] + " ");
                    aa++;
                }
                parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
            }
            strSql.Append(where);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parm.ToArray());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //获取所有的Id
        public List<int> GetAllId(List<string> fields, List<object> values)
        {
            List<int> list = new List<int>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select SortId from Menu where 1=1  ");
            List<SqlParameter> parm = new List<SqlParameter>();
            for (int i = 0; i < fields.Count; i++)
            {
                if (fields[i].StartsWith("like_"))
                {
                    strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                }
                else if (fields[i].StartsWith("not_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                }
                else if (fields[i].StartsWith("lt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                }
                else if (fields[i].StartsWith("gt_"))
                {
                    strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                }
                else
                {
                    strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                }
                parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
            }
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parm.ToArray()).Tables[0];
            if (dt == null || dt.Rows.Count <= 0)
            {
                return list;
            }
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                if (dt.Rows[j]["SortId"] != null && dt.Rows[j]["SortId"].ToString() != "")
                {
                    list.Add(Convert.ToInt32(dt.Rows[j]["SortId"].ToString()));
                }
            }
            return list;
        }
            /// <summary>
            /// 获取隐藏菜单的权限控制列表
            /// </summary>
            /// <param name="roleId">限定的角色编号</param>
            /// <returns></returns>
            public DataTable GetHiddenMenuList(int roleId)
            {
                return DbHelperSQL.GetDataTable(@" select m.*,p.* from Menu as m 
            left join (select * from PermissionList where RoleId=@RoleId) as p on m.MenuID=p.MenuID
            where m.Status=2 
            ", new SqlParameter("@RoleId", roleId));
            }
        /// <summary>
        /// 获得数据列表(一定要有RoleId参数)
        /// </summary>
        public DataSet GetListUnionPermissionList(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select *  FROM (
                            select m.*,TE.sumFun
                            from Menu m
                            left join (
                            select sum(FuncID) as sumFun,m.MenuID
                            from Menu m
                            left join PermissionList p on m.MenuID=p.MenuID 
                            where RoleId=@RoleId
                            group by m.MenuID 
                            )  TE on TE.MenuID=m.MenuID
                            ) T where 1=1 ");
            List<SqlParameter> parm = new List<SqlParameter>(); if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("RoleId"))
                    { 
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获得数据列表(左侧菜单部分)
        /// </summary>
        public DataSet GetListPermission(List<string> fields, List<object> values)
        {
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append(@"select * from Menu m 
                            where m.MenuID in 
	                            (
	                            /*寻找顶级的部分*/
	                            select me.ParentMenu from Menu me
	                            where me.MenuID in  
		                            (
		                            select g.ParentMenu from PermissionList p 
		                            left join Menu g on p.MenuID=g.MenuID 
		                            where p.RoleId=@RoleId and g.Status='0'
		                            ) 
	                            /*寻找二级的部分*/
	                            union 
	                            select mt.ParentMenu from PermissionList ps 
	                            left join Menu mt on ps.MenuID=mt.MenuID
		                            where ps.RoleId=@RoleId and mt.Status='0'
	                            /*寻找功能菜单部分*/
	                            union
	                            select MenuID from PermissionList pt
	                            where pt.RoleId=@RoleId 
	                            )   ");
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("RoleId"))
                    {
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }      
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
        /// <summary>
        /// 获得数据列表(左侧菜单部分)
        /// </summary>
        public DataSet GetListPermission2(List<string> fields, List<object> values, string parentId)
        {
            StringBuilder strSql = new StringBuilder();
            List<SqlParameter> parm = new List<SqlParameter>();
            strSql.Append(@" select * from Menu m 
                            where m.MenuID in 
	                            (
	                            /*寻找顶级的部分*/
	                            select me.ParentMenu from Menu me
	                            where me.MenuID in  
		                            (
		                            select g.ParentMenu from PermissionList p 
		                            left join Menu g on p.MenuID=g.MenuID 
		                            where p.RoleId=@RoleId and g.Status='0' and (p.PerStatus &1=1) and p.PUserId='" + parentId + @"'
		                            ) 
	                            /*寻找二级的部分*/
	                            union 
	                            select mt.ParentMenu from PermissionList ps 
	                            left join Menu mt on ps.MenuID=mt.MenuID
		                            where ps.RoleId=@RoleId and mt.Status='0'  and (ps.PerStatus &1=1)  and ps.PUserId='" + parentId + @"'
	                            /*寻找功能菜单部分*/
	                            union
	                            select MenuID from PermissionList pt
	                            where pt.RoleId=@RoleId and (pt.PerStatus &1=1) and pt.PUserId='" + parentId + @"'
	                            )   ");
            if (fields != null && fields.Count > 0)
            {
                for (int i = 0; i < fields.Count; i++)
                {
                    if (fields[i].StartsWith("like_"))
                    {
                        strSql.Append(" AND  charindex(@" + fields[i].Substring(5) + "," + fields[i].Substring(5) + ") > 0  ");
                    }
                    else if (fields[i].StartsWith("RoleId"))
                    {
                    }
                    else if (fields[i].StartsWith("not_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(4) + "!=@" + fields[i].Substring(4) + "  ");
                    }
                    else if (fields[i].StartsWith("lt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + "<=@" + fields[i].Substring(3) + "  ");
                    }
                    else if (fields[i].StartsWith("gt_"))
                    {
                        strSql.Append("  AND " + fields[i].Substring(3) + ">=@" + fields[i].Substring(3) + "  ");
                    }
                    else
                    {
                        strSql.Append(" AND " + fields[i] + "=@" + fields[i] + " ");
                    }
                    parm.Add(new SqlParameter("@" + fields[i].Substring(fields[i].IndexOf('_') + 1), values[i]));
                }
            }
            return DbHelperSQL.Query(strSql.ToString(), parm.ToArray());
        }
    }
}
